package com.kco.generate.config;

import com.mysql.jdbc.Driver;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

/**
 * Created by 666666 on 2017/9/26.
 */
public final class SqlUtils {

    private static JdbcTemplate jdbcTemplate;
    private static SimpleDriverDataSource dataSource;

    public static final String sqlUrl = "jdbc:mysql://127.0.0.1:3306/information_schema?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull";
    public static final String sqlUserName = "root";
    public static final String sqlPassword = "123456";

//    public static final String sqlDriver = "com.mysql.jdbc.Driver";
    private static JdbcTemplate jdbcTemplate() throws Exception {
        if (jdbcTemplate != null){
            return jdbcTemplate;
        }
        dataSource = new SimpleDriverDataSource(new Driver(),sqlUrl, sqlUserName, sqlPassword);
        jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }

    public static TableBean queryByDBAndTableName(String dbName, String tableName) throws Exception {
        String sql1 = "SELECT COLUMN_COMMENT,COLUMN_NAME,DATA_TYPE,COLUMN_KEY ,CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT " +
            "FROM COLUMNS \n" +
                "WHERE TABLE_SCHEMA = ? AND table_name = ?";
        TableBean tableBean = new TableBean();
        List<ColumnsBean> result = jdbcTemplate().query(sql1, new Object[]{dbName, tableName}, (rs, rowNum) -> {

            ColumnsBean build = ColumnsBean.builder().columnComment(getComment(rs.getString("COLUMN_COMMENT")))
                .columnName(rs.getString("COLUMN_NAME"))
                .jdbcType(rs.getString("DATA_TYPE"))
                .propertyName(column2property(rs.getString("COLUMN_NAME")))
                .javaType(jdbc2javaType(rs.getString("DATA_TYPE")))
                .defaultValue(rs.getString("COLUMN_DEFAULT"))
                .isNullAble(StringUtils.equalsIgnoreCase(rs.getString("IS_NULLABLE"), "YES"))
                .maxSize(rs.getInt("CHARACTER_MAXIMUM_LENGTH"))
                .build();
            if (StringUtils.equals("PRI", rs.getString("COLUMN_KEY"))){
                tableBean.setPrimaryColumnKey(build.getColumnName());
                tableBean.setPrimaryPropertyKey(build.getPropertyName());
                tableBean.setPrimaryPropertyKeyColumnComment(build.getColumnComment());
            }
            return build;
        });

        tableBean.setColumnsBeans(result);
        tableBean.setTableName(tableName);
        String clssName = table2class(tableName);
        tableBean.setNoPrefixTableName(table2NoPrefixTableName(tableName));
        tableBean.setClassName(clssName);
        tableBean.setClassNameVariable(clssName.substring(0, 1).toLowerCase() + clssName.substring(1));
        String sql2 = "SELECT table_comment FROM TABLES WHERE TABLE_SCHEMA = ? AND table_name = ? limit 1";
        String tableComment = jdbcTemplate.query(sql2, new Object[]{dbName,tableName}, new ResultSetExtractor<String>() {
            @Override
            public String extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()){
                    return rs.getString("table_comment");
                }
                return "";

            }
        });
        tableBean.setTableComment(tableComment.replace("表",""));
        return tableBean;
    }

    private static String table2NoPrefixTableName(String tableName) {
        int fristCharIndex = tableName.indexOf('_');
        return tableName.substring(fristCharIndex + 1).toUpperCase();
    }

    private static String getComment(String column_comment) {
        if (StringUtils.isBlank(column_comment)) {
            return "";
        }
        return column_comment.trim().split(":")[0];
    }

    private static String jdbc2javaType(String jdbcType) {
        switch (jdbcType){
            case "timestamp": return "Date";
            default:
                return "String";
        }
    }

    public static String column2property(String input) {
        String[] split = input.split("_");
        System.out.println(Arrays.toString(split));
        for (int i = 1; i < split.length; i ++){
            split[i] = split[i].substring(0, 1).toUpperCase() + split[i].substring(1);
        }
        return StringUtils.join(split);
    }
    public static String table2class(String input) {
        int fristCharIndex = input.indexOf('_');
        System.out.println(fristCharIndex);
        input = input.substring(fristCharIndex + 1);
        String[] split = input.split("_");
        System.out.println(Arrays.toString(split));
        for (int i = 0; i < split.length; i ++){
            split[i] = split[i].substring(0, 1).toUpperCase() + split[i].substring(1);
        }
        return StringUtils.join(split);
    }

//    public static void main(String[] args) {
//        System.out.println(table2class("t_user_info"));
//    }
}
